package dao;

import helper.StringHelper;

import java.sql.ResultSet;
import java.util.ArrayList;

import util.Config;

import model.CityModel;
import model.CountryModel;

/**
 * DB-Lesezugriffe auf CityModel
 * 
 * @author Katharina Matei
 * @Version 1.0.0
 */
public class CityDAO implements CityDAOInterface {

	/**
	 * Liest alle Cities zu einer Country-ID aus der Datenbank
	 * 
	 * @param String countryID
	 * @return ArrayList<CityModel>
	 * @throws DBException
	 */
	@Override
	public ArrayList<CityModel> getCities(String countryID) throws DBException {
		String dbStatement = "SELECT * FROM citymodel WHERE country_id = '"
				+ StringHelper.addSlashes(countryID) + "' ORDER BY name ";
		return getDBDaten(dbStatement);
	}

	/**
	 * Fuegt einen City-Datensatz in die Datenbank ein
	 * 
	 * @param CityModel
	 * @throws DBException
	 */
	@Override
	public void insert(CityModel city) throws DBException {
		String dbStatement = "INSERT INTO citymodel (city_id, name, country_id, country_name, lat, lon ) VALUES ('"
				+ city.getCityID()
				+ "','"
				+ city.getName()
				+ "','"
				+ city.getCountryID()
				+ "','"
				+ city.getCountryName()
				+ "', "
				+ 0 + " , " + 0 + " )";
		executeDBStatement(dbStatement);
	}

	/**
	 * Aendert die Namen der Cities, die der uebergebenen city_id entsprechen
	 * 
	 * @param CityModel
	 * @throws DBException
	 */
	@Override
	public void update(CityModel city) throws DBException {
		String dbStatement = "UPDATE citymodel set name = '" + city.getName()
				+ "' WHERE city_id = '" + city.getCityID() + "'";
		executeDBStatement(dbStatement);
	}

	/**
	 * Loeschen der Cities aus der Datenbank, die der uebergebenen city_id
	 * entsprechen
	 * 
	 * @param CityModel
	 * @throws DBException
	 */
	@Override
	public void delete(CityModel city) throws DBException {
		String dbStatement = "DELETE FROM citymodel WHERE city_id = '"
				+ city.getCityID() + "'";
		executeDBStatement(dbStatement);
	}

	/**
	 * Liest alle Cities aus der Datenbank
	 * 
	 * @return ArrayList<CityModel>
	 * @throws DBException
	 */
	@Override
	public ArrayList<CityModel> getCityModelAll() throws DBException {
		String dbStatement = "SELECT * FROM citymodel";
		return getDBDaten(dbStatement);
	}

	/**
	 * Liest die Cities zu einem Country aus der Datenbank
	 * 
	 * @param CountryModel
	 * @return ArrayList<CityModel>
	 * @throws SQLException
	 */
	@Override
	public ArrayList<CityModel> getCityByCountry(CountryModel country)
			throws DBException {
		String dbStatement = "SELECT * FROM citymodel WHERE country_name LIKE '"
				+ country.getName() + "'";
		return getDBDaten(dbStatement);
	}

	/**
	 * Liest einen City-Datensatz zu einer City-ID aus der Datenbank
	 * 
	 * @param String cityID
	 * @return CityModel
	 * @throws DBException
	 */
	@Override
	public CityModel readCity(String cityID) throws DBException {
		CityModel citymodel = null;
		String dbStatement = "SELECT * FROM citymodel WHERE city_id = '"
				+ StringHelper.addSlashes(cityID) + "'";
		try {
			ResultSet resultSet = DBAccess.getInstance().query(dbStatement);
			while (resultSet.next()) {
				citymodel = new CityModel();
				citymodel.setCityID(resultSet.getString("city_id"));
				citymodel.setName(resultSet.getString("name"));
				citymodel.setCountryID(resultSet.getString("country_id"));
				citymodel.setCountryName(resultSet.getString("country_name"));
				citymodel.setCoordinates(resultSet.getDouble("lat"),
						resultSet.getDouble("lon"));
			}
		} catch (Exception e) {
			if (Config.DEBUG) {
				e.printStackTrace();
			}
			throw new DBException("Fehler in : " + dbStatement, e);
		}
		return citymodel;
	}

	/**
	 * Fuehrt die DB-Anweisung aus
	 * 
	 * @param String dbStatement
	 * @throws DBException
	 */
	private void executeDBStatement(String dbStatement) throws DBException {
		try {
			DBAccess.getInstance().execute(dbStatement);
		} catch (Exception e) {
			if (Config.DEBUG) {
				e.printStackTrace();
			}
			throw new DBException("Fehler in : " + dbStatement, e);
		}
	}
	
	/**
	 * Fuellt ArrayList mit Daten aus der DB
	 * 
	 * @param String dbStatement
	 * @return ArrayList<CityModel>
	 * @throws DBException
	 */
	private ArrayList<CityModel> getDBDaten(String dbStatement)
			throws DBException {
		ArrayList<CityModel> list = null;
		try {
			ResultSet resultSet = DBAccess.getInstance().query(dbStatement);
			int columns = resultSet.getMetaData().getColumnCount();
			list = new ArrayList<CityModel>(columns);
			while (resultSet.next()) {
				CityModel citymodel = new CityModel();
				citymodel.setCityID(resultSet.getString("city_id"));
				citymodel.setName(resultSet.getString("name"));
				citymodel.setCountryID(resultSet.getString("country_id"));
				citymodel.setCountryName(resultSet.getString("country_name"));
				citymodel.setCoordinates(resultSet.getDouble("lat"),
						resultSet.getDouble("lon"));
				list.add(citymodel);
			}
		} catch (Exception e) {
			if (Config.DEBUG) {
				e.printStackTrace();
			}
			throw new DBException("Fehler in : " + dbStatement, e);
		}
		return list;
	}
	
}
